<?php
ini_set('display_errors',1);
$db_server="localhost";
$db_user="wordpressuser720";
$db_password="ei3Lyy1id]bD";
$db_name="wordpress720"; 

 mysql_connect($db_server,$db_user,$db_password); 
 mysql_select_db($db_name); 
 
$sql="select ID from wp_users";

$result=mysql_query($sql);

if($result!=NULL){
 if(mysql_num_rows($result)>0){ 
	while($row=mysql_fetch_array($result)){ 
		email($row['ID']);  
	} 
	}
	mysql_free_result($result); 
} 
 mysql_close(); 



function email($user){
	$validoNegativo1=tank1($user);
	$validoNegativo2=tank2($user);
	$validoNegativo3=tank3($user);
	 if($validoNegativo1 >2 || $validoNegativo2 >2 || $validoNegativo3 >2){
	 $mensaje.= "Dear user, our systems reveals that you have 7 days with non valid or incorrect information. See detail below.<br>
	 The thank:  ";
	  if($validoNegativo1 >2){
	  $mensaje.= "CLR DSL  ";
		}
	  if($validoNegativo2 >2){
	  $mensaje.= "GASOLINE 87 OCT E-10 UNLEADED  ";
		}
	  if($validoNegativo3 >2){
	  $mensaje.= "GASOLINE 93 OCT E-10 HITEST  ";
		}
	
		$mensaje.= "<br> had reported negative values, please check it and contact the administrator. <br>Thanks";
		$encabezados = "From: info@plugingroup.com\nReply-To: info@plugingroup.com\nContent-Type: text/html; charset=iso-8859-1" ;

		mail('EnvironmentalNotification@carrollfuel.net', 'Monthly Report', $mensaje, $encabezados) or die ("No se ha podido enviar tu mensaje. Ha ocurrido un error") ;
		//desactivar el usuario update al mysql
		echo "<p>Tu mensaje a sido enviado con este contenido:</p>" ;
		echo "<strong><b>$mensaje</b></strong>" ;
		
		require( './wp-load.php' );
		 
		// id of user to update
		$user_id = $user;
		 
		/*
		 * Basic list of user roles
		 *
		 * administrator
		 * editor
		 * author
		 * contributor
		 * subscriber
		 *
		 */
		 
		// user role to update to
		$new_role = 'subscriber';
		 
		// update user role using wordpress function
		wp_update_user( array ('ID' => $user_id, 'role' => $new_role ) ) ;
	 }
 }
function tank1(){
require('connect.php');
$hoy = date('Y-m-j');
$nuevafecha = strtotime ( '-7 day' , strtotime ( $hoy ) ) ;
$desde = date ( 'Ymd' , $nuevafecha );
$hasta = date('Y-m-d');

$rows=NULL;
$validoNegativo1=0;
	$arrayDatos = array();
	$nuevafecha = date ( 'Ymd' ,strtotime ( '+1 day' , strtotime($hasta)));
	
	$fdesde = str_replace("-", "", $desde);
	$fhasta = str_replace("-", "", $nuevafecha);

	$mes1 = date('M Y',strtotime($desde));
	$mes2 = date('M Y',strtotime($hasta));
	$final = date('j M Y',strtotime($hasta));
	$mismo_mes = false;
	//To know if the report envolves only the same month
	if($mes1==$mes2){
		$mismo_mes = true;
	}
	$query = "SELECT ID as Id, 
		DAY(TrnDate) as 'Day of Month',
		YEAR(TrnDate) as 'Year',
		ReadTime as 'Time of Read',
		OpenGal as 'Opening Gallons',
		ProdNo as 'Product',
		Water as 'Water',
		Sales as 'Sales',
		GalDeliv as 'Gross Deliveries',
		SUBSTRING(CONVERT(VARCHAR(11), TrnDate, 113), 4, 8) as 'Date',
		CONVERT(VARCHAR(8), TrnDate, 24) as 'Hour',
		Comments as 'Comments'
		FROM EnvironmentalDaily
		where CONVERT(VARCHAR(8), TrnDate, 112)   >= '$fdesde'
		and  CONVERT(VARCHAR(8), TrnDate, 112)  <= '$fhasta'
		and TankID = 'BCD8091EBFE8483AA7749DC7CF66C23C'
		order by TrnDate asc";

	$rows = sqlsrv_query( $conn, $query);
	$arrayfechas = array();	
	$registros = array();

	while( $row = sqlsrv_fetch_array( $rows, SQLSRV_FETCH_ASSOC) ) {		
		$registros[] = $row;
	}
	foreach($registros as $row) {
		if(!in_array($row['Date'],$arrayfechas)){
			$arrayfechas[]= $row['Date'];
		}
	}
	if($mismo_mes){
		$arrayfechas = array();
		$arrayfechas[] = $mes1 ;
	}

	$arrayMovimientos = array();
	$cantidad = count ($registros);
	$k=0;
	foreach($registros as $row) {
		$inventory = $row["Opening Gallons"]-$row["Sales"] +$row["Gross Deliveries"];
		$siguiente = $registros [$k+1];
		$daily = $siguiente["Opening Gallons"]-$inventory;

	 if($row["Day of Month"]==1){
	 	$monthly_over = $daily;
	 } else {
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly']+$daily;
	}
	if($cantidad==$k+1){
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly'];
		$daily = 0;
	}
	$arrayMovimientos[]= array('id' => $row["Id"], 'day' => $row["Day of Month"], 'time'=> $row["Time of Read"],'hour'=>$row["Hour"],
	'opening'=> $row["Opening Gallons"], 'product'=> $row["Product"],'water'=> $row["Water"], 'sales'=>$row["Sales"],
	'gross'=>$row["Gross Deliveries"],'book'=>$inventory,'daily'=>$daily,'monthly'=>$monthly_over,'Date'=>$row["Date"],
	'comments'=>$row["Comments"]);
	$k++;
}
	foreach($arrayfechas as $fecha) {
		$movimientos = array();
		foreach($arrayMovimientos as $row) {
			if($row['Date']==$fecha) {
				$movimientos[]=$row;
			}
		}
		$arrayDatos[] = array('fecha'=>$fecha,'movimientos'=>$movimientos);
	}
	sqlsrv_close($conn);


if(!empty($arrayDatos)){
	foreach($arrayDatos as $dato){	
 
$k=0;
//calculos
$sales =0;
$monthly_total =0;
$cantidad = count($dato['movimientos']);
$j=0;

foreach ($dato['movimientos'] as $row) {
 	$final = date('Ymd',strtotime($fhasta));
 	$fecha = date('Ymd',strtotime($row["day"]." ".$row["Date"]));
	if($final > $fecha){
	if($row["monthly"]<0){
	  $validoNegativo1++;	
	}
		$sales +=$row["sales"];
		if($cantidad==$j+1){
			$pasado = $dato['movimientos'][$j];
			$monthly_total = $pasado['monthly'];
		}
	}
	$j++;
}
 
 $leak = ($sales*0.01)+130;


 } 
 
 }
return $validoNegativo1;
}

function tank2(){
require('connect.php');
$hoy = date('Y-m-j');
$nuevafecha = strtotime ( '-7 day' , strtotime ( $hoy ) ) ;
$desde = date ( 'Ymd' , $nuevafecha );
$hasta = date('Y-m-d');

$rows=NULL;

	$arrayDatos = array();
	$nuevafecha = date ( 'Ymd' ,strtotime ( '+1 day' , strtotime($hasta)));
	$validoNegativo2=0;
	$fdesde = str_replace("-", "", $desde);
	$fhasta = str_replace("-", "", $nuevafecha);

	$mes1 = date('M Y',strtotime($desde));
	$mes2 = date('M Y',strtotime($hasta));
	$final = date('j M Y',strtotime($hasta));
	$mismo_mes = false;
	//To know if the report envolves only the same month
	if($mes1==$mes2){
		$mismo_mes = true;
	}
	$query = "SELECT ID as Id, 
		DAY(TrnDate) as 'Day of Month',
		YEAR(TrnDate) as 'Year',
		ReadTime as 'Time of Read',
		OpenGal as 'Opening Gallons',
		ProdNo as 'Product',
		Water as 'Water',
		Sales as 'Sales',
		GalDeliv as 'Gross Deliveries',
		SUBSTRING(CONVERT(VARCHAR(11), TrnDate, 113), 4, 8) as 'Date',
		CONVERT(VARCHAR(8), TrnDate, 24) as 'Hour',
		Comments as 'Comments'
		FROM EnvironmentalDaily
		where CONVERT(VARCHAR(8), TrnDate, 112)   >= '$fdesde'
		and  CONVERT(VARCHAR(8), TrnDate, 112)  <= '$fhasta'
		and TankID = 'F2858F8E8A93474698407F080CD6B061'
		order by TrnDate asc";

	$rows = sqlsrv_query( $conn, $query);
	$arrayfechas = array();	
	$registros = array();

	while( $row = sqlsrv_fetch_array( $rows, SQLSRV_FETCH_ASSOC) ) {		
		$registros[] = $row;
	}
	foreach($registros as $row) {
		if(!in_array($row['Date'],$arrayfechas)){
			$arrayfechas[]= $row['Date'];
		}
	}
	if($mismo_mes){
		$arrayfechas = array();
		$arrayfechas[] = $mes1 ;
	}

	$arrayMovimientos = array();
	$cantidad = count ($registros);
	$k=0;
	foreach($registros as $row) {
		$inventory = $row["Opening Gallons"]-$row["Sales"] +$row["Gross Deliveries"];
		$siguiente = $registros [$k+1];
		$daily = $siguiente["Opening Gallons"]-$inventory;

	 if($row["Day of Month"]==1){
	 	$monthly_over = $daily;
	 } else {
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly']+$daily;
	}
	if($cantidad==$k+1){
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly'];
		$daily = 0;
	}
	$arrayMovimientos[]= array('id' => $row["Id"], 'day' => $row["Day of Month"], 'time'=> $row["Time of Read"],'hour'=>$row["Hour"],
	'opening'=> $row["Opening Gallons"], 'product'=> $row["Product"],'water'=> $row["Water"], 'sales'=>$row["Sales"],
	'gross'=>$row["Gross Deliveries"],'book'=>$inventory,'daily'=>$daily,'monthly'=>$monthly_over,'Date'=>$row["Date"],
	'comments'=>$row["Comments"]);
	$k++;
}
	foreach($arrayfechas as $fecha) {
		$movimientos = array();
		foreach($arrayMovimientos as $row) {
			if($row['Date']==$fecha) {
				$movimientos[]=$row;
			}
		}
		$arrayDatos[] = array('fecha'=>$fecha,'movimientos'=>$movimientos);
	}
	sqlsrv_close($conn);


if(!empty($arrayDatos)){
	foreach($arrayDatos as $dato){	
 
$k=0;
//calculos
$sales =0;
$monthly_total =0;
$cantidad = count($dato['movimientos']);
$j=0;

foreach ($dato['movimientos'] as $row) {
 	$final = date('Ymd',strtotime($fhasta));
 	$fecha = date('Ymd',strtotime($row["day"]." ".$row["Date"]));
	if($final > $fecha){
	if($row["monthly"]<0){
	  $validoNegativo2++;	
	}
	
		$sales +=$row["sales"];
		if($cantidad==$j+1){
			$pasado = $dato['movimientos'][$j];
			$monthly_total = $pasado['monthly'];
		}
	}
	$j++;
}
 
 $leak = ($sales*0.01)+130;


 } 
 
 }

return $validoNegativo2;

}


function tank3(){
require('connect.php');
$hoy = date('Y-m-j');
$nuevafecha = strtotime ( '-7 day' , strtotime ( $hoy ) ) ;
$desde = date ( 'Ymd' , $nuevafecha );
$hasta = date('Y-m-d');

$rows=NULL;

	$arrayDatos = array();
	$nuevafecha = date ( 'Ymd' ,strtotime ( '+1 day' , strtotime($hasta)));
	
	$fdesde = str_replace("-", "", $desde);
	$fhasta = str_replace("-", "", $nuevafecha);

	$mes1 = date('M Y',strtotime($desde));
	$mes2 = date('M Y',strtotime($hasta));
	$final = date('j M Y',strtotime($hasta));
	$mismo_mes = false;
	//To know if the report envolves only the same month
	if($mes1==$mes2){
		$mismo_mes = true;
	}
$validoNegativo3=0;
	$query = "SELECT ID as Id, 
		DAY(TrnDate) as 'Day of Month',
		YEAR(TrnDate) as 'Year',
		ReadTime as 'Time of Read',
		OpenGal as 'Opening Gallons',
		ProdNo as 'Product',
		Water as 'Water',
		Sales as 'Sales',
		GalDeliv as 'Gross Deliveries',
		SUBSTRING(CONVERT(VARCHAR(11), TrnDate, 113), 4, 8) as 'Date',
		CONVERT(VARCHAR(8), TrnDate, 24) as 'Hour',
		Comments as 'Comments'
		FROM EnvironmentalDaily
		where CONVERT(VARCHAR(8), TrnDate, 112)   >= '$fdesde'
		and  CONVERT(VARCHAR(8), TrnDate, 112)  <= '$fhasta'
		and TankID = 'CA7200DE3FFD4C24BC7BC2C5F6EBD787'
		order by TrnDate asc";

	$rows = sqlsrv_query( $conn, $query);
	$arrayfechas = array();	
	$registros = array();

	while( $row = sqlsrv_fetch_array( $rows, SQLSRV_FETCH_ASSOC) ) {		
		$registros[] = $row;
	}
	foreach($registros as $row) {
		if(!in_array($row['Date'],$arrayfechas)){
			$arrayfechas[]= $row['Date'];
		}
	}
	if($mismo_mes){
		$arrayfechas = array();
		$arrayfechas[] = $mes1 ;
	}

	$arrayMovimientos = array();
	$cantidad = count ($registros);
	$k=0;
	foreach($registros as $row) {
		$inventory = $row["Opening Gallons"]-$row["Sales"] +$row["Gross Deliveries"];
		$siguiente = $registros [$k+1];
		$daily = $siguiente["Opening Gallons"]-$inventory;

	 if($row["Day of Month"]==1){
	 	$monthly_over = $daily;
	 } else {
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly']+$daily;
	}
	if($cantidad==$k+1){
		$pasado = $arrayMovimientos[$k-1];
		$monthly_over = $pasado['monthly'];
		$daily = 0;
	}
	$arrayMovimientos[]= array('id' => $row["Id"], 'day' => $row["Day of Month"], 'time'=> $row["Time of Read"],'hour'=>$row["Hour"],
	'opening'=> $row["Opening Gallons"], 'product'=> $row["Product"],'water'=> $row["Water"], 'sales'=>$row["Sales"],
	'gross'=>$row["Gross Deliveries"],'book'=>$inventory,'daily'=>$daily,'monthly'=>$monthly_over,'Date'=>$row["Date"],
	'comments'=>$row["Comments"]);
	$k++;
}
	foreach($arrayfechas as $fecha) {
		$movimientos = array();
		foreach($arrayMovimientos as $row) {
			if($row['Date']==$fecha) {
				$movimientos[]=$row;
			}
		}
		$arrayDatos[] = array('fecha'=>$fecha,'movimientos'=>$movimientos);
	}
	sqlsrv_close($conn);


if(!empty($arrayDatos)){
	foreach($arrayDatos as $dato){	
 
$k=0;
//calculos
$sales =0;
$monthly_total =0;
$cantidad = count($dato['movimientos']);
$j=0;

foreach ($dato['movimientos'] as $row) {
 	$final = date('Ymd',strtotime($fhasta));
 	$fecha = date('Ymd',strtotime($row["day"]." ".$row["Date"]));
	if($final > $fecha){
	if($row["monthly"]<0){
	  $validoNegativo3++;	
	}
		$sales +=$row["sales"];
		if($cantidad==$j+1){
			$pasado = $dato['movimientos'][$j];
			$monthly_total = $pasado['monthly'];
		}
	}
	$j++;
}
 
 $leak = ($sales*0.01)+130;


 } 
 
 }
 
 return $validoNegativo3;
}
 


 
 